package com.duowei.diandan.sql;

public class DataSyncSql {

    public static String createTableSql() {
        return "IF EXISTS(SELECT * " +
                "          FROM sysobjects " +
                "          WHERE name = 'android_data_sync' AND xtype = 'U') " +
                "  BEGIN " +
                "    DROP TABLE android_data_sync; " +
                "  END; " +
                " " +
                "IF NOT EXISTS(SELECT * " +
                "              FROM sysobjects " +
                "              WHERE name = 'android_data_sync' AND xtype = 'U') " +
                "  BEGIN " +
                "    CREATE TABLE android_data_sync ( " +
                "      id         INT NOT NULL IDENTITY (1, 1) PRIMARY KEY, " +
                "      table_name VARCHAR(100), " +
                "      checksum   BIGINT, " +
                "      is_update   VARCHAR(1) " +
                "    ) " +
                "  END|";
    }

    public static String deleteTableSql() {
        return "DELETE FROM android_data_sync";
    }

    public static String checkSyncSql(String tableName) {
        return "DELETE FROM android_data_sync " +
                "WHERE id NOT IN ( " +
                "  SELECT min(id) " +
                "  FROM android_data_sync " +
                "  GROUP BY table_name " +
                "  );" +
                " " +
                "DECLARE @checksum BIGINT; " +
                "IF NOT EXISTS(SELECT * " +
                "              FROM android_data_sync " +
                "              WHERE table_name = '" + tableName + "') " +
                "  BEGIN " +
                "    SELECT @checksum = sum(cast(checksum(*) AS BIGINT)) " +
                "    FROM " + tableName + "; " +
                " " +
                "    INSERT INTO android_data_sync (table_name, checksum, is_update) " +
                "    VALUES ('" + tableName + "', @checksum, '0'); " +
                "  END " +
                "ELSE " +
                "  BEGIN " +
                "    SELECT @checksum = sum(cast(checksum(*) AS BIGINT)) " +
                "    FROM " + tableName + "; " +
                " " +
                "    IF @checksum != (SELECT checksum " +
                "                     FROM android_data_sync " +
                "                     WHERE table_name = '" + tableName + "') " +
                "      BEGIN " +
                "        UPDATE android_data_sync " +
                "        SET is_update = '1', " +
                "          checksum   = @checksum " +
                "        WHERE table_name = '" + tableName + "'; " +
                "      END " +
                "  END|";
    }

    public static String setSyncedSql(String tableName) {
        return "UPDATE android_data_sync " +
                "SET is_update = '0' " +
                "WHERE table_name = '" + tableName + "'|";
    }

    public static String querySql() {
        return "SELECT * FROM android_data_sync WHERE is_update = '1'|";
    }
}
